package com.ruoyi.mymod.utils;

import com.fasterxml.jackson.databind.ObjectMapper;
import oracle.jdbc.OracleTypes;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

/*
数据库应用工具
 */
public class Dbutil {

    public static void os( Object ... objs){
        for(int i=0;i<objs.length;i++){

            System.out.println(objs[i].toString());
        }


    }


    /*
    查询返回的结果
     */
    public static class R{
        public int rowCnt;
        public int colCnt;
        public Object[] header;
        public Object[] headerN;
        public Object[][] body;
        public boolean success;
        public String json;

        public R(ResultSet rs){
            if (rs==null)
                success=false;

            try {
                ResultSetMetaData rm = rs.getMetaData();
                colCnt=rm.getColumnCount();
                header=new Object[colCnt];
                headerN=new Object[colCnt];
                for (int i = 0; i < colCnt; i++) {
                    header[i] = rm.getColumnTypeName(i + 1);
                    headerN[i] = rm.getColumnName(i + 1);
                }

                rs.setFetchSize(1000);
                ArrayList<Object[]> rows=new ArrayList<>();

                while(rs.next()){
                   Object[] os=new Object[colCnt];
                   for (int j = 0; j < colCnt; j++) {
                       os[j]=rs.getObject(j+1);
                   }
                   rows.add(os);
                }

                rowCnt=rows.size();
                body=new Object[rowCnt][colCnt];
                for(int i=0;i<rowCnt;i++){
                    body[i]=rows.get(i);
                }
                success=true;
                json();
            }
            catch ( SQLException e){
                success=false;
            }
            finally {
            }
        }

        public R json(){
            try {
                ObjectMapper objectMapper = new ObjectMapper();
                json = objectMapper.writeValueAsString(body);
            }catch (com.fasterxml.jackson.core.JsonProcessingException ex){

            }
            finally {
                return this;
            }
        }

        public void show(){
            for (Object[] os :body) {
                for (Object o : os) {
                    System.out.print(o + " ");
                }
                System.out.println("");
            }
        }
    }

    /*
     数据库应用参数
   */
    public static class Qp{
        public String host="10.104.8.7";
        public String port="1521";
        public String db="orcl";
        public String url;
        public String usr="psi";
        public String pwd="psi";
        public Connection conn;
        public Map<String,Object[]> beforePros=new HashMap<>();
        public String sqlStr;
        public Object[] os;
        private static Map<Integer,Integer> retTypeMap =new HashMap<>();  //专为oracle使用
        static{
            retTypeMap.put(1,OracleTypes.VARCHAR);
            retTypeMap.put(2,OracleTypes.NUMBER);
            retTypeMap.put(3,OracleTypes.DATE);
            retTypeMap.put(4,OracleTypes.CURSOR);
        }

        public  Qp(){}
        public Qp(Connection conn,String sqlStr){
            this.conn=conn;
            this.sqlStr=sqlStr;
        }

        public Qp setUsrpwd(String usr,String pwd){
            this.usr=usr;
            this.pwd=pwd;
            return this;
        }

        public Qp setHostPortDb(String host,String port,String db){
            this.host=host;
            this.port=port;
            this.db=db;
            return this;
        }

        public Qp setSqlStr(String sqlStr){
            this.sqlStr=sqlStr;
            return this;
        }

        public Qp setOs(Object[] os){
            this.os=os;
            return this;
        }

        public Qp setP(Object ... os){
            this.os=os;
            return this;
        }

        private void genConn(){
            if (conn==null){
                if (url==null){
                    url= DbConn.getOraUrl(2,host,port,db);
                }
                conn= DbConn.getConnOra(url,usr,pwd);
            }
        }

        private void before(){
            for(String p: beforePros.keySet())
                DbOper.procedure(conn,p,beforePros.get(p));
        }

        public R select(){
            genConn();
            before();
            return DbOper.select(conn,sqlStr);
        }
        public R selectP(){
            genConn();
            before();
            return DbOper.select(conn,sqlStr,os);
        }

        /**
         * 执行oracle的dml语句
         **/
        public int exe(){
            genConn();
            before();
            return DbOper.exeSql(conn,sqlStr);
        }
        public int exeP(){
            genConn();
            before();
            return DbOper.exeSql(conn,sqlStr,os);
        }

        /**
         * 取oracle的函数返回值
             retType 1 字符串  2 数字  3 日期  4 游标
         **/
        public Object func(Integer retType,String funcName){
            genConn();
            before();
            return  DbOper.function(conn,funcName,retTypeMap.get(retType));
        }
        public Object funcP(Integer retType,String funcName){
            genConn();
            before();
            return  DbOper.function(conn,funcName,retTypeMap.get(retType),os);
        }

        /**
         * 取oracle的过程
         retType 1 字符串  2 数字  3 日期  4 游标
         **/
        public Object proc(String proName){
            genConn();
            before();
            return  DbOper.procedure(conn,proName);
        }

        /**
         * 取oracle的过程
         retType 1 字符串  2 数字  3 日期  4 游标
         **/
        public Object procP(String proName){
            genConn();
            before();
            return  DbOper.procedure(conn,proName,os);
        }



        public void closeConn(){
            DbAssit.closeConn(conn);
        }
    }

    public static class DbOper {

        //Logger log=Logger.getLogger(Dbutils.class);

        private static final int FETCH_SIZE = 3000;

        // 执行oracle带参的Sql语句
        public static int exeSql(Connection conn, String sqlStr, Object... paras) {
            //System.out.println("2");
            //System.out.println(paras.length);
            int ret = 0;
            PreparedStatement pstmt = null;
            try {
                pstmt = conn.prepareStatement(sqlStr);
                for (int i = 0; i < paras.length; i++) {
                    pstmt.setObject(i + 1, paras[i]);
                }
                ret = pstmt.executeUpdate();
                if (ret > 0)
                    DbAssit.commit(conn);
            } catch (Exception e) {
                System.out.println(e.toString());
            } finally {
                DbAssit.closeStmt(pstmt);
            }
            return ret;
        }


        // 执行oracleb函数 (conn 连接，funName 函数名，retType返回值类型 ，os参数
        public static Object function(Connection conn, String funName, int retType, Object... os) {
            return exePro(conn, true, retType, funName, os);
        }

        // 执行oracle过程 (conn 连接，proName 过程名，os 过程参数)
        public static Object procedure(Connection conn, String proName, Object... os) {
            return (Boolean) exePro(conn, false, 1, proName, os);
        }

        /*
         * 函数 {?=call funName(?,?,?)} 过程 {call proName(?,?)} 执行oracle的方法(函数和过程) conn连接
         * isF=true函数 isF=false过程 retType 返回值类型，用 OracleTypes.NUMBER之类的表示较方便，对函数才有意义
         * proName 过程和函数名 os 参数，个数可变
         */
        public static Object exePro(Connection conn, Boolean isF, int retType, String proName, Object... os) {
            Object ret = null;
            String sql;

            sql = (isF) ? "{?=call " : "{call ";

            sql += proName;

            int paraLen = os.length;

            if (paraLen > 0)
                sql += "(?";

            if (paraLen > 1)
                for (int i = 1; i < paraLen; i++) {
                    sql += ",?";
                }

            if (paraLen > 0)
                sql += ")";

            sql += "}";

            CallableStatement cstmt = null;
            try {
                cstmt = conn.prepareCall(sql);
                //System.out.println(sql);
                if (isF)
                    cstmt.registerOutParameter(1, retType);
                int offset = isF ? 2 : 1;
                for (int i = 0; i < paraLen; i++) {
                    cstmt.setObject(i + offset, os[i]);
                }

                ret = cstmt.execute();

                if (isF) {
                    if (retType == OracleTypes.CURSOR)
                        ret = new R((ResultSet) cstmt.getObject(1));
                    else
                        ret = cstmt.getObject(1);
                }

            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                DbAssit.closeStmt(cstmt);
            }
            return ret;
        }


        public static R select(Connection conn, String sqlStr, Object... paras) {
            PreparedStatement pstmt = null;
            ResultSet rs = null;
            R r=null;
            try {
                pstmt = conn.prepareStatement(sqlStr);
                for (int i = 0; i < paras.length; i++) {
                    pstmt.setObject(i + 1, paras[i]);
                }
                rs = pstmt.executeQuery();
                r=new R(rs);
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                DbAssit.releaseDB(rs, pstmt, null);
            }
            return r;
        }
    }

    public static class DbAssit {

        //begin transaction
        //处理数据库事务的
        //提交事务
        public static void commit(Connection connection){
            if(connection != null){
                try {
                    connection.commit();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        //回滚事务
        public static void rollback(Connection connection){
            if(connection != null){
                try {
                    connection.rollback();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        //开始事务
        public static void beginTx(Connection connection){
            if(connection != null){
                try {
                    connection.setAutoCommit(false);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        //end transaction


        //begin close Connection,Statement,ResultSet
        public static void closeConn(Connection conn) {
            releaseDB(null,null,conn);
        }

        public static void closeStmt(Statement statement) {
            releaseDB(null,statement,null);
        }

        public static void closeRst(ResultSet resultSet) {
            releaseDB(resultSet,null,null);
        }

        public static void releaseDB(ResultSet resultSet, Statement statement,
                                     Connection connection) {

            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (connection != null) {
                try {
                    //数据库连接池的 Connection 对象进行 close 时
                    //并不是真的进行关闭, 而是把该数据库连接会归还到数据库连接池中.
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        //end close Connection,Statement,ResultSet


    }

    public static class DbConn {

        /*
         * 5种数据库驱动   oracle,sqlserver,access,mysql,sqlite
         */
        static {
            try {
                Class.forName("oracle.jdbc.driver.OracleDriver");
                /*
                Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
                Class.forName("com.hxtt.sql.access.AccessDriver");
                Class.forName("com.mysql.jdbc.Driver");
                Class.forName("org.sqlite.JDBC");*/
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        }


        //begin  oracle connect
        /*
         * oracle有三种连接方式   sid(实例 instance)、ServiceName(service)、 tnsName(transparence Network Substrate透明网络底层)
         *1、host(主机),port(端口号)，sid(实例)
         *2、host(主机)，port，service(服务)	 *
         *3、tnsName(tns中一般配好了host,sid,port)
         *jdbc:oracle:thin:@localhost:1521:SID
         *jdbc:oracle:thin:@//localhost:1521/SERVICENAME
         */

        public static final int OraSid=1;
        public static final int OraSer=2;
        public static final int OraTns=3;
        //三种连接方式连接串写法不一样
        public static String getOraUrl(int flag, String host, String port, String db) {
            String pre = "jdbc:oracle:thin:@";
            switch (flag) {
                case OraSid:
                    return pre + host + ":" + port + ":" + db;
                case OraSer:
                    return pre + "//" + host + ":" + port + "/" + db;
                case OraTns:
                    return pre + host;
                default:
                    return null;
            }
        }

        public static Connection getConnOra(String url, String user, String password) {
            try {
                return DriverManager.getConnection(url,user,password);
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
        }
        //end oracle

        //begin mysql
        public static String getMysqlUrl(String host,String port,String db) {
            String pre="jdbc:mysql://";
            return pre+host+":"+port+"/"+db+"?useSSL=false";
        }
        public static Connection getConnMysql(String url,String user,String password) {
            try {
                return DriverManager.getConnection(url,user,password);
            }
            catch (Exception e) {
                e.printStackTrace();
                return null;
            }
        }
        //end mysql


        //begin Sqlserver
        public static String getSqlserverUrl(String host,String port,String db) {
            String pre="jdbc:sqlserver://";
            return pre+host+":"+port+";database="+db;
        }
        public static Connection getConnSqlserver(String url,String user,String password) {
            try {
                return DriverManager.getConnection(url,user,password);
            }
            catch (Exception e) {
                e.printStackTrace();
                return null;
            }
        }
        //end Sqlserver


        //begin Access
        public static Connection getConnAccess(String dbFileName,String password) {
            String pre="jdbc:Access:///";
            try {
                return DriverManager.getConnection(pre+dbFileName,"",password);
            }
            catch (Exception e) {
                e.printStackTrace();
                return null;
            }

        }
        //end Access

        //begin sqlite
        public static Connection getConnSqlite(String dbFileName) throws Exception {
            String pre="jdbc:sqlite:";
            try {
                return DriverManager.getConnection(pre+dbFileName);
            }
            catch (Exception e) {
                e.printStackTrace();
                return null;
            }
        }
        //end

        public static boolean close(Connection conn) {
            if (conn!=null) {
                try {
                    conn.close();
                    return true;
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                    return false;
                }
            }
            return false;
        }

    }

    public static abstract class DataToTable {
        private Object[] oo;
        private Connection conn;
        private String sqlStr;
        protected String para;


        public DataToTable(Object[] oo, Connection conn, String sqlStr, String para) {
            this.oo=oo;
            this.conn=conn;
            this.sqlStr=sqlStr;
            this.para=para;
        }

        public void execute() {
            PreparedStatement pstmt = null;
            try {
                pstmt = conn.prepareStatement(sqlStr);
                for (int i = 0; i < oo.length; i++) {
                    Object[] o = (Object[]) oo[i];

                    addParameter(o, pstmt); // 写入参数

                    pstmt.addBatch();
                    if ((i + 1) % 1000 == 0) {
                        pstmt.executeBatch();
                        pstmt.clearBatch();
                    }
                }
                if (oo.length % 1000 != 0) {
                    pstmt.executeBatch();
                    pstmt.clearBatch();
                }
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            } finally {
                DbAssit.releaseDB(null, pstmt, null);
            }
        }
        // 写入参数
        public abstract void addParameter(Object[] o, PreparedStatement pstmt) throws SQLException;
    }
}
